-- @owner: opentestcase003
-- @date: 2022-07-13
-- @testpoint: covar_samp函数在merge into using语句的测试，合理报错

--step1: 创建表；expect:成功
drop table if exists t_ustore_function_covarsamp_0024;
create table  t_ustore_function_covarsamp_0024(col_1 integer,col_2 bigint,col_3 float8,col_4 decimal(12,6),col_5 bool,col_6 char(30),col_7 varchar2(50),col_8 varchar(30),col_9 interval day to second,col_10 timestamp,col_11 date,col_12 date,col_13 timestamp without time zone, col_14 blob,col_15 clob,col_16 int[]) with (storage_type=ustore);

drop sequence if exists s_ustore_function_covarsamp_0024;
create sequence s_ustore_function_covarsamp_0024 increment by 1 start with 10;

begin
for i in 1..50 loop
      insert into t_ustore_function_covarsamp_0024 values(i,s_ustore_function_covarsamp_0024.nextval+i,i+445.255,98*0.99*i,true,lpad('abc','30','@')::varchar, lpad('abc','50','b')::varchar2,lpad('abc','30','@')::varchar,(interval '4 5:12:10.222' day to second(3)),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk')::clob,'{32,535,5645645,6767,76,67,56,48,979,978,7}');
    end loop;
end;
/
insert into t_ustore_function_covarsamp_0024 select * from t_ustore_function_covarsamp_0024;
insert into t_ustore_function_covarsamp_0024 select * from t_ustore_function_covarsamp_0024;

--step2: covar_samp函数在merge into using语句的测试；expect:失败
drop table if exists t2_ustore_function_covarsamp_0024;
create table t2_ustore_function_covarsamp_0024 with (storage_type=ustore) as select distinct col_1,col_2,col_3,col_4,col_5 from t_ustore_function_covarsamp_0024;
merge into t_ustore_function_covarsamp_0024 t1 using t2_ustore_function_covarsamp_0024 t2 on (t1.col_2=t2.col_2) when matched then update set t1.col_1=covar_samp(t2.col_1,t2.col_1) when not matched then insert(t1.col_2) values(2000);
merge into t_ustore_function_covarsamp_0024 t1 using t2_ustore_function_covarsamp_0024 t2 on (t1.col_2!=t2.col_2) when matched then update set t1.col_1=1000 when not matched then insert(t1.col_2) values(covar_samp(t2.col_1,t2.col_1));

--step3: 清理表；expect:成功
drop table t_ustore_function_covarsamp_0024;
drop table if exists t2_ustore_function_covarsamp_0024;
drop sequence if exists s_ustore_function_covarsamp_0024;